﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace ScheduleMaker.DB
{

    class TableCreator
    {
        //wartosci funkcji generate(int x)
        public const int GENERATE_SCHOOL_TABLES = 56465;
        public const int GENERATE_UNIVERSITY_TABLES = 87463;

        //----------------------------------------------------
        //Dane to bazy przekazywane w konstruktorze
        private String SERVER = "";
        private String DATABASE = "";
        private String USER = "";
        private String PASSWORD = "";
        private MySql.Data.MySqlClient.MySqlConnection conn = null;
        //----------------------------------------------------
        private static String CREATE_TABLE_BEGIN = "CREATE TABLE";
        private static String CREATE_TABLE_END = ");";
        private static String SCHOOL_FILE = "school-tables.sql";
        private static String UNIVERSITY_FILE = "university-tables.sql";
        private List<String> sqlInserts = new List<String>();
        private Boolean connectionInConstructor = false;
        public TableCreator(String server_p, String database_p, string user_p, string password_p) {
            this.SERVER = server_p;
            this.DATABASE = database_p;
            this.USER = user_p;
            this.PASSWORD = password_p;
        }
        public TableCreator(MySql.Data.MySqlClient.MySqlConnection conn_p) {
            this.conn = conn_p;
            this.connectionInConstructor = true;
        }

        private void openConnection()
        {
            string myConnectionString;

            myConnectionString = "server=" + this.SERVER + ";uid=" + this.USER + ";" +
                "pwd=" + this.PASSWORD + ";database=" + this.DATABASE + ";";

            try
            {
                this.conn = new MySql.Data.MySqlClient.MySqlConnection();
                this.conn.ConnectionString = myConnectionString;
                this.conn.Open();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                throw new Exception("Cannot establish database connection!");
            }
        }
        public void generate(int schoolORuniversity)
        {
            if (schoolORuniversity == TableCreator.GENERATE_UNIVERSITY_TABLES)
            {
                readFile(UNIVERSITY_FILE);
            }
            else if (schoolORuniversity == TableCreator.GENERATE_SCHOOL_TABLES)
            {
                readFile(SCHOOL_FILE);
            }
            if (this.conn == null) {
                openConnection();
            }
            executeInserts();
            if(!this.connectionInConstructor){
            closeConnection();
            }
        }
        public void readFile(String file_p)
        {
            String fileContent = File.ReadAllText(Directory.GetCurrentDirectory() + @"\SQLSchema\" + file_p);

            string[] lines = System.Text.RegularExpressions.Regex.Split(fileContent, CREATE_TABLE_BEGIN);
            //Debug.Print(lines.Length.ToString());


            foreach (String line in lines)
            {
                if (line.Contains(CREATE_TABLE_END))
                {
                    String toAdd = CREATE_TABLE_BEGIN + line.Substring(0, line.IndexOf(CREATE_TABLE_END)) + CREATE_TABLE_END;
                    toAdd = toAdd.Replace("\r\n", "");
                    this.sqlInserts.Add(toAdd);
                }
            }
            //Debug.Print(sqlInserts.Count.ToString());
        }
        public void executeInserts()
        {
            foreach (String insert in this.sqlInserts)
            {
                MySqlCommand myCommand = new MySqlCommand(insert);
                myCommand.Connection = this.conn;
                myCommand.ExecuteNonQuery();
            }
        }
        public void closeConnection()
        {
            try
            {
                this.conn.Close();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
